<?php

declare(strict_types=1);

namespace app\model;

use think\facade\Db;

/**
 * 数据库
 */
class Mysql extends Db
{
    /**
     * 数据类型 variable
     *
     * @var array
     */
    public static array $mysqlType = [
        "int", "bigint", "decimal", "enum", "set", "json", "double", "tinytext", "varchar", "longtext", "text"
    ];

    /**
     * 查询数据表 function
     *
     * @param string $field
     * @return array
     */
    public static function SelectTable(string $field = "*"): array
    {
        $prefix = \config("database.connections.mysql.prefix"); //表前缀
        $database = \config("database.connections.mysql.database"); //数据库名
        $sql = "SELECT $field FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$database'";
        $res = Mysql::query($sql);
        if ($res) {
            foreach ($res as $k => $v) {
                $res[$k]["TABLE_NAME"] = str_replace($prefix, "", $v["TABLE_NAME"]);
            }
        }
        return $res;
    }

    /**
     * 创建数据表 function
     *
     * @param string $table 表名 
     * @param string $COMMENT 表备注名
     * @param boolean $default_id 主键id开关
     * @param boolean $default_cuTime 时间开关
     * @param boolean $default_del 删除开关
     * @return bool
     */
    public static function CreateTable(
        string $table,
        string $COMMENT,
        bool $default_id = true,
        bool $default_cuTime = true,
        bool $default_del = true
    ): bool {
        $prefix = \config("database.connections.mysql.prefix");
        $sqlTable = $prefix . $table;
        $str = "";
        if ($default_id) {
            $str .= "`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',";
            if ($default_cuTime) {
                $str .= "`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
                        `update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',";
            }
            if ($default_del) {
                $str .= "`delete_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '删除时间',";
            }
            $str .= "PRIMARY KEY (`id`)";
        }
        Mysql::query("CREATE TABLE `$sqlTable` (
            $str
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='{$COMMENT}';");
        return true;
    }

    /**
     * 编辑数据表 function
     *
     * @param string $table
     * @param array $arr
     * @return boolean
     */
    public static function editTable(string $table, array $arr): bool
    {
        $prefix = \config("database.connections.mysql.prefix");
        $table = $prefix . $table;
        $sql = "ALTER TABLE $table RENAME TO " . $prefix . $arr["table"] . ", COMMENT='" . $arr["comment"] . "';";
        Mysql::query($sql);
        return true;
    }

    /**
     * 删除数据表 function
     *
     * @param string $table 数据表
     * @return boolean
     */
    public static function delTable(string $table): bool
    {
        $prefix = \config("database.connections.mysql.prefix");
        $table = $prefix . $table;
        Mysql::query("DROP TABLE $table");
        return true;
    }

    /**
     * 查询表字段信息 function
     *
     * @param string $table 表
     * @param string $field 要查询的信息
     * @return array
     */
    public static function selectField(string $table, string $field = "*"): array
    {
        $prefix = \config("database.connections.mysql.prefix");
        $table = $prefix . $table;
        $database = \config("database.connections.mysql.database"); //数据库名
        //COLUMN_NAME,DATA_TYPE,COLUMN_KEY,COLUMN_COMMENT,COLUMN_DEFAULT 
        $sql = "select $field from INFORMATION_SCHEMA.COLUMNS where table_name = '$table' and table_schema = '$database' order by ORDINAL_POSITION asc";
        $res = Mysql::query($sql);
        return $res;
    }

    /**
     * 增加、编辑、删除表字段 function
     *
     * @param string $table 表
     * @param array $fileds 字段组
     * @param string $state 增加-ADD COLUMN;编辑-CHANGE COLUMN;删除-DROP COLUMN;
     * @return boolean
     */
    public static function audField(string $table, array $fileds, string $state = "ADD COLUMN"): bool
    {
        $prefix = \config("database.connections.mysql.prefix");
        $table = $prefix . $table;
        $field = self::fieldTidy($fileds, $state);
        Mysql::query("ALTER TABLE {$table} $field");
        return true;
    }

    /**
     * 字段整理 function
     *
     * @param array $data 字段组
     * @param string $add true为添加反之编辑
     * @return string
     */
    private static function fieldTidy(array $data, string $add = "ADD COLUMN"): string
    {
        $arr = [];
        self::val($data, $add);
        foreach ($data as $k => $v) {
            if ($add == "ADD COLUMN" || $add == "CHANGE COLUMN ") {
                if (is_string($v["limit"]) && ctype_digit($v["limit"])) {
                    $v["limit"] = (int)$v["limit"];
                }
                $comment = $v["comment"];
                $old_field = "";
                $field = self::field($v["field"]);
                $type = self::type($v["type"]);
                $limit = self::limit($v["type"], $v["limit"]);
                $default = self::default($v["type"], $v["default"]);
                if ($add == "CHANGE COLUMN ") {
                    $old_field = $v["old_field"];
                }
                $arr[] = "$add $field $old_field $type$limit $default COMMENT '$comment'";
            } else {
                $arr[] = "$add " . $v;
            }
        }
        return implode(",", $arr);
    }

    /**
     * 备份数据表 function
     *
     * @param string $table 表名
     * @return integer|boolean
     */
    public static function backups(string $table): int|bool
    {
        $prefix = \config("database.connections.mysql.prefix");
        $table = $prefix . $table;
        $sql = "";
        $sql .= "-- --------------------------------------------\n";
        $sql .= "-- Table structure for `$table`\n";
        $sql .= "-- --------------------------------------------\n";
        $sql .= "DROP TABLE IF EXISTS `$table`;\n";
        $res = Mysql::query("SHOW CREATE TABLE $table");
        $sql .= $res[0]['Create Table'] . ";\n";
        $sql .= "-- --------------------------------------------\n";
        $sql .= "-- Records for `$table`\n";
        $sql .= "-- --------------------------------------------\n";
        $result = Mysql::query("SELECT * FROM $table");

        // 遍历查询结果并生成SQL语句
        foreach ($result as $row) {
            $sql .= "INSERT INTO `$table` VALUES (";

            foreach ($row as $field) {
                if (is_null($field)) {
                    $sql .= "NULL,";
                } else {
                    $field = addslashes((string)$field);
                    $field = preg_replace("/\r\n/", "\\r\\n", $field);
                    $sql .= "'$field',";
                }
            }

            $sql = rtrim($sql, ",");
            $sql .= ");\n";
        }

        // 获取 backup 目录路径
        $path = public_path("/static/backup/");

        if (!file_exists($path)) {
            mkdir($path, 0777, true);
        }

        $file = $table . "-" . date('YmdHis') . '.sql';

        // 将 SQL 语句保存到文件中
        return file_put_contents($path . $file, $sql);
    }

    /**
     * 数据文件还原 function
     *
     * @param string $file 文件路径
     * @return boolean
     */
    public static function restore(string $file): bool
    {
        // 读取sql文件内容
        $sqlStr = file_get_contents($file);
        // 删除注释
        $sqlStr = preg_replace('/--(.*)/', '', $sqlStr);
        // 切割单条sql语句
        $sqlArr = explode(';', $sqlStr);
        // print_r($sqlArr);
        Db::startTrans();
        // $batchSize = 100; // 每批次SQL语句执行数量
        try {
            foreach ($sqlArr as $k => $v) {
                if (trim($v) != "") {
                    Db::query(trim($v) . ";");
                } else {
                    continue;
                }
            }
            Db::commit();
            return true;
        } catch (\PDOException $e) {
            Db::rollBack();
            throw new \ErrorException($e->getMessage());
        }
    }

    /**
     * 字段过滤 function
     *
     * @param string $field
     * @return string
     */
    private static function field(string $field): string
    {
        return trim($field);
    }

    /**
     * 类型过滤 function
     *
     * @param string $type
     * @return string
     */
    private static function type(string $type): string
    {
        return trim($type);
    }

    /**
     * 长度过滤 function
     *
     * @param string $type 类型
     * @param integer|array|string $limit 长度
     * @return string
     */
    private static function limit(string $type, int|array|string $limit): string
    {
        if (in_array(strtolower($type), ["tinytext", "text", "longtext", "json"])) {
            $limit = "";
        } else if (is_array($limit)) {
            foreach ($limit as $k => $v) {
                if (ctype_digit($v)) {
                    $limit[$k] = (int)$v;
                } else {
                    $limit[$k] = "'" . $v . "'";
                }
            }
            $limit = "(" . implode(",", $limit) . ")";
        } else {
            $limit = "(" . $limit . ")";
        }
        return $limit;
    }

    /**
     * 是否为空过滤 function
     *
     * @param string $type
     * @param string $default
     * @return string
     */
    private static function default(string $type, string $default): string
    {
        if (in_array(strtolower($type), ["tinytext", "text", "longtext", "json"])) {
            return "";
        } else {
            return "NOT NULL DEFAULT '$default'";
        }
    }

    /**
     * 验证 function
     *
     * @param array $arr
     * @param string $state
     * @return void
     */
    private static function val(array $arr, string $state = "ADD COLUMN")
    {
        $errMsg = "";
        foreach ($arr as $k => $v) {
            if ($state == "DROP COLUMN") {
                break;
            }
            switch ($state) {
                case "ADD COLUMN":
                    if (!isset($v["field"])) {
                        $errMsg = "缺少字段：field";
                        break;
                    }
                    if (!isset($v["limit"])) {
                        $errMsg = "缺少字段：limit";
                        break;
                    }
                    if (!isset($v["type"])) {
                        $errMsg = "缺少字段：type";
                        break;
                    }
                    if (!isset($v["comment"])) {
                        $errMsg = "缺少字段：comment";
                        break;
                    }
                    if (!isset($v["default"])) {
                        $v["default"] = "";
                    }
                    break;
                case "CHANGE COLUMN":
                    if ($state == "CHANGE COLUMN" && !isset($v["old_field"])) {
                        $errMsg = "缺少字段：old_field";
                        break;
                    }
                    break;
            }
        }
        if ($errMsg != "") {
            throw new \ErrorException($errMsg);
        }
    }
}
